---
title: "SQL-PRV-02: Provider collection with a caller-supplied WHERE clause"
summary: Retrieve providers using a caller-supplied WHERE predicate
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PRV-02 Provider collection with a caller-supplied WHERE clause

Retrieves the set of `PROVIDER` records matching an arbitrary caller-supplied `WHERE` clause
(`m_WhereClause`) appended verbatim to the base select, with no bind parameters or validation.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PRV-02 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant, verbatim caller-supplied WHERE) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProviderCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER](../database-tables.md#provider) | SELECT | `PRIMARY_TAX_ID`, `PROVIDER_SEQ_NUM`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER` |
| [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id) | JOIN (LEFT OUTER) | `TAX_ID`, `TAX_ID_DISPLAY_DESCR`, `REGISTERED_NAME` |

## Sample SQL

> **Security note:** `m_WhereClause` is appended verbatim from the caller
> ([BR-PRV-003](../business-rules.md#br-prv-003-provider-set-retrieval-uses-unparameterized-sql)).
> The example below shows a representative caller-supplied predicate.

```sql
-- Provider collection with a caller-supplied WHERE clause
SELECT
  PRV.PRIMARY_TAX_ID        AS PrimaryTaxID,
  PRV.PROVIDER_SEQ_NUM      AS ProviderID,
  PRV.UPDATE_DATE           AS UpdateDate,
  PRV.UPDATE_NUM            AS UpdateNum,
  PRV.UPDATE_USER           AS UpdateUser,
  TAX.TAX_ID_DISPLAY_DESCR  AS FormattedPrimaryTaxID,
  TAX.REGISTERED_NAME       AS RegisteredName
FROM PROVIDER PRV
  LEFT OUTER JOIN PROVIDER_TAX_ID TAX
    ON TAX.TAX_ID = PRV.PRIMARY_TAX_ID
WHERE TAX.REGISTERED_NAME_UPPER_CASE LIKE 'ACME%'
```

## Operations and processes
- [Retrieve provider set](../operations/retrieve-provider-set.md)

## Business rules
- [BR-PRV-003: Unparameterized provider collection SQL](../business-rules.md#br-prv-003-provider-set-retrieval-uses-unparameterized-sql)

## Sources
- SAIF.Business.View.Provider/clsProviderCollection.vb